package com.terren.statistic.execute.excel;

import java.io.File;
import java.util.List;

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.terren.statistic.common.util.ExcelUtil;
import com.terren.statistic.db.entity.CustomizedStat;

public class KeywordExcel {

	private static String OFFICIAL = "官媒";// 官媒
	private static String PORTAL = "门户";// 门户
	private static String[] OFFARR = { "日期", "网站名称", "发稿数", "访客数" };
	private static String[] PORARR = { "日期", "网站名称", "发稿数", "评论数" };

	/**
	 * 
	 * @param offList
	 * @param porList
	 * @param name
	 * @return
	 * @throws Exception
	 */
	public String createExcel(List<CustomizedStat> offList, List<CustomizedStat> porList, String name, String path)
			throws Exception {
		// 标题 来源站点 发布时间 PV UV 文章内容 URL

		/** 创建Excel的工作书册 Workbook,对应到一个excel文档 **/
		XSSFWorkbook excelBook = new XSSFWorkbook();
		/** 创建单元格样式，居中 **/
		XSSFCellStyle style = excelBook.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		/** 创建官媒sheet,对应到一个excel文档的tab **/
		XSSFSheet sheet1 = excelBook.createSheet(OFFICIAL);
		/** 设置excel每列宽度 **/
		sheet1.setColumnWidth(0, 15*256);
		sheet1.setColumnWidth(1, 12*256);
		sheet1.setColumnWidth(2, 10*256);
		sheet1.setColumnWidth(3, 10*256);

		/** ============================== 第一行 标题 ============================== **/
		/** 创建Excel的sheet的一行 **/
		XSSFRow row1 = sheet1.createRow(0);
		/** 创建一个Excel的单元格 **/
		XSSFRichTextString stringValue1 = null;

		XSSFCell cell1 = row1.createCell(0);
		cell1.setCellStyle(style);
		stringValue1 = new XSSFRichTextString(OFFARR[0]);
		cell1.setCellValue(stringValue1);

		cell1 = row1.createCell(1);
		cell1.setCellStyle(style);
		stringValue1 = new XSSFRichTextString(OFFARR[1]);
		cell1.setCellValue(stringValue1);

		cell1 = row1.createCell(2);
		cell1.setCellStyle(style);
		stringValue1 = new XSSFRichTextString(OFFARR[2]);
		cell1.setCellValue(stringValue1);

		cell1 = row1.createCell(3);
		cell1.setCellStyle(style);
		stringValue1 = new XSSFRichTextString(OFFARR[3]);
		cell1.setCellValue(stringValue1);

		/** ============================== 第二行 数据 ============================== **/
		if (offList == null || offList.isEmpty()) {// 无结果
			/** 新的一行 **/
			row1 = sheet1.createRow(1);
			/** ==================== 列 ==================== **/
			cell1 = row1.createCell(0);
			stringValue1 = new XSSFRichTextString("无结果");
			cell1.setCellValue(stringValue1);
		} else {
			int number = 0;
			for (CustomizedStat doc : offList) {
				number++;
				/** 新的一行 **/
				row1 = sheet1.createRow(number);
				/** ==================== 列 ==================== **/
				/** 1列 **/
				cell1 = row1.createCell(0);
				cell1.setCellStyle(style);
				cell1.setCellValue(doc.getDay());

				/** 2列 **/
				cell1 = row1.createCell(1);
				cell1.setCellStyle(style);
				cell1.setCellValue(doc.getSource());

				/** 3列 **/
				cell1 = row1.createCell(2);
				cell1.setCellValue(doc.getArticleCnt());

				/** 4列 **/
				cell1 = row1.createCell(3);
				cell1.setCellValue(doc.getVisitCnt());
			}
		}

		/** 创建门户sheet,对应到一个excel文档的tab **/
		XSSFSheet sheet2 = excelBook.createSheet(PORTAL);
		/** 设置excel每列宽度 **/
		sheet2.setColumnWidth(0, 15*256);
		sheet2.setColumnWidth(1, 12*256);
		sheet2.setColumnWidth(2, 10*256);
		sheet2.setColumnWidth(3, 10*256);
		/** ============================== 第一行 标题 ============================== **/
		/** 创建Excel的sheet的一行 **/
		XSSFRow row2 = sheet2.createRow(0);
		/** 创建一个Excel的单元格 **/
		XSSFRichTextString stringValue2 = null;

		XSSFCell cell2 = row2.createCell(0);
		cell2.setCellStyle(style);
		stringValue2 = new XSSFRichTextString(PORARR[0]);
		cell2.setCellValue(stringValue2);

		cell2 = row2.createCell(1);
		cell2.setCellStyle(style);
		stringValue2 = new XSSFRichTextString(PORARR[1]);
		cell2.setCellValue(stringValue2);

		cell2 = row2.createCell(2);
		cell2.setCellStyle(style);
		stringValue2 = new XSSFRichTextString(PORARR[2]);
		cell2.setCellValue(stringValue2);

		cell2 = row2.createCell(3);
		cell2.setCellStyle(style);
		stringValue2 = new XSSFRichTextString(PORARR[3]);
		cell2.setCellValue(stringValue2);

		/** ============================== 第二行 数据 ============================== **/
		if (porList == null || porList.isEmpty()) {// 无结果
			/** 新的一行 **/
			row2 = sheet2.createRow(1);
			/** ==================== 列 ==================== **/
			cell2 = row2.createCell(0);
			stringValue2 = new XSSFRichTextString("无结果");
			cell2.setCellValue(stringValue2);
		} else {
			int number = 0;
			for (CustomizedStat doc : porList) {
				number++;
				/** 新的一行 **/
				row2 = sheet2.createRow(number);
				/** ==================== 列 ==================== **/
				/** 1列 **/
				cell2 = row2.createCell(0);
				cell2.setCellStyle(style);
				cell2.setCellValue(doc.getDay());

				/** 2列 **/
				cell2 = row2.createCell(1);
				cell2.setCellStyle(style);
				cell2.setCellValue(doc.getSource());

				/** 3列 **/
				cell2 = row2.createCell(2);
				cell2.setCellValue(doc.getArticleCnt());

				/** 4列 **/
				cell2 = row2.createCell(3);
				cell2.setCellValue(doc.getCommentCnt());
			}
		}

		ExcelUtil eu = new ExcelUtil();
		File excelFile = eu.writeFile(excelBook, name, path);
		String excelPath = excelFile.getAbsolutePath();
		return excelPath;
	}

}
